﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using jutehui.IDAL;
using Maticsoft.DBUtility;
using System.Collections.Generic;//Please add references
namespace jutehui.SQLServerDAL
{
    /// <summary>
    /// 数据访问类:Product
    /// </summary>
    public partial class Product : IProduct
    {
        public Product()
        {
        }
        #region  BasicMethod

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ProductId", "Product");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(long ProductId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Product");
            strSql.Append(" where ProductId=@ProductId");
            SqlParameter[] parameters = {
					new SqlParameter("@ProductId", SqlDbType.BigInt)
			};
            parameters[0].Value = ProductId;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public long Add(jutehui.Model.Product model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Product(");
            strSql.Append("Pname,Plink,Price,ImgUrl,Description,SoldCount,Source,UpdateTime,Memo,FromWeb,ProductTypeId,TypeId,PriceType,UpdateTimeStr,ImgUrl2,ImgUrl3,ImgUrl4)");
            strSql.Append(" values (");
            strSql.Append("@Pname,@Plink,@Price,@ImgUrl,@Description,@SoldCount,@Source,@UpdateTime,@Memo,@FromWeb,@ProductTypeId,@TypeId,@PriceType,@UpdateTimeStr,@ImgUrl2,@ImgUrl3,@ImgUrl4)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Pname", SqlDbType.Text),
					new SqlParameter("@Plink", SqlDbType.Text),
					new SqlParameter("@Price", SqlDbType.VarChar,50),
					new SqlParameter("@ImgUrl", SqlDbType.Text),
					new SqlParameter("@Description", SqlDbType.Text),
					new SqlParameter("@SoldCount", SqlDbType.Int,4),
					new SqlParameter("@Source", SqlDbType.VarChar,150),
					new SqlParameter("@UpdateTime", SqlDbType.DateTime),
					new SqlParameter("@Memo", SqlDbType.Text),
					new SqlParameter("@FromWeb", SqlDbType.Text),
					new SqlParameter("@ProductTypeId", SqlDbType.BigInt,8),
					new SqlParameter("@TypeId", SqlDbType.BigInt,8),
					new SqlParameter("@PriceType", SqlDbType.NVarChar,10),
					new SqlParameter("@UpdateTimeStr", SqlDbType.Char,50),
					new SqlParameter("@ImgUrl2", SqlDbType.Text),
					new SqlParameter("@ImgUrl3", SqlDbType.Text),
					new SqlParameter("@ImgUrl4", SqlDbType.Text)};
            parameters[0].Value = model.Pname;
            parameters[1].Value = model.Plink;
            parameters[2].Value = model.Price;
            parameters[3].Value = model.ImgUrl;
            parameters[4].Value = model.Description;
            parameters[5].Value = model.SoldCount;
            parameters[6].Value = model.Source;
            parameters[7].Value = model.UpdateTime;
            parameters[8].Value = model.Memo;
            parameters[9].Value = model.FromWeb;
            parameters[10].Value = model.ProductTypeId;
            parameters[11].Value = model.TypeId;
            parameters[12].Value = model.PriceType;
            parameters[13].Value = model.UpdateTimeStr;
            parameters[14].Value = model.ImgUrl2;
            parameters[15].Value = model.ImgUrl3;
            parameters[16].Value = model.ImgUrl4;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt64(obj);
            }
        }
        /// <summary>
        /// 整张表写入数据库 add by szr
        /// </summary>
        /// <param name="Tname"></param>
        /// <param name="dt"></param>
        /// <param name="exm"></param>
        /// <returns></returns>
        public bool SaveTableData(string Tname, DataTable dt, out string exm)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                DataTable productsdt = null;

                SqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    SqlCommand command = conn.CreateCommand();
                    command.Connection = conn;
                    command.Transaction = transaction;
                    using (SqlBulkCopy sqlcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
                    {
                        sqlcopy.BulkCopyTimeout = 300;
                        sqlcopy.DestinationTableName = Tname;
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            productsdt = dt.Clone();
                            productsdt.Clear();

                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                sqlcopy.ColumnMappings.Add(i, dt.Columns[i].ColumnName);
                            }
                            foreach (DataRow dr in dt.Rows)
                            { 
                                command.CommandText = "select count(*) from product where DeleteTime is null   and  Memo='" + dr["Memo"].ToString() + "'";
                                var result = command.ExecuteScalar();
                                if (result == null || result.ToString() == "0")
                                {
                                    dr["UpdateTime"] = DateTime.Now;
                                    dr["UpdateTimeStr"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                                    productsdt.Rows.Add(dr.ItemArray);
                                }
                            }
                        }
                        //处理 取消重复数据
                        if (productsdt != null && productsdt.Rows.Count > 0)
                        {
                            sqlcopy.WriteToServer(productsdt);
                            transaction.Commit();
                        }
                    }
                    exm = "";
                    return true;
                }
                catch (Exception ex)
                {
                    exm = ex.Message;
                    conn.Close();
                    return false;
                    // transaction.Rollback(); 
                    // throw ex; 

                }
            }
        }

        /// <summary>
        /// 整张表写入数据库  淘宝特卖  九块九 add by szr
        /// </summary>
        /// <param name="Tname"></param>
        /// <param name="dt"></param>
        /// <param name="exm"></param>
        /// <returns></returns>
        public bool SaveTableDataTmall(string Tname, DataTable dt, out string exm,string isTmall)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                DataTable productsdt = null;

                SqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    SqlCommand command = conn.CreateCommand();
                    command.Connection = conn;
                    command.Transaction = transaction;
                    using (SqlBulkCopy sqlcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
                    {
                        sqlcopy.BulkCopyTimeout = 300;
                        sqlcopy.DestinationTableName = Tname;
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            productsdt = dt.Clone();
                            productsdt.Clear();

                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                sqlcopy.ColumnMappings.Add(i, dt.Columns[i].ColumnName);
                            }
                            foreach (DataRow dr in dt.Rows)
                            {
                                command.CommandText = "select count(*) from product where DeleteTime is null and (Down is null or Down=0) and TypeId=" + isTmall + " and  Memo='" + dr["Memo"].ToString() + "'";
                                var result = command.ExecuteScalar();
                                if (result == null || result.ToString() == "0")
                                {
                                    dr["UpdateTime"] = DateTime.Now;
                                    dr["UpdateTimeStr"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
                                    productsdt.Rows.Add(dr.ItemArray);
                                }
                            }
                        }
                        //处理 取消重复数据
                        if (productsdt != null && productsdt.Rows.Count > 0)
                        {
                            sqlcopy.WriteToServer(productsdt);
                            transaction.Commit();
                        }
                    }
                    exm = "";
                    return true;
                }
                catch (Exception ex)
                {
                    exm = ex.Message;
                    conn.Close();
                    return false;
                    // transaction.Rollback(); 
                    // throw ex; 

                }
            }
        }

        /// <summary>
        /// 整张表写入数据库 小时榜操作 add by szr
        /// </summary>
        /// <param name="Tname"></param>
        /// <param name="dt"></param>
        /// <param name="exm"></param>
        /// <returns></returns>
        public bool SaveTableDataToHourTop(string Tname, DataTable dt, out string exm)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();
                DataTable productsdt = null;

                SqlTransaction transaction = conn.BeginTransaction();
                try
                {
                    SqlCommand command = conn.CreateCommand();
                    command.Connection = conn;
                    command.Transaction = transaction;
                    using (SqlBulkCopy sqlcopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
                    {
                        sqlcopy.BulkCopyTimeout = 300;
                        sqlcopy.DestinationTableName = Tname;
                        if (dt != null && dt.Rows.Count > 0)
                        {
                            productsdt = dt.Clone();
                            productsdt.Clear();

                            for (int i = 0; i < dt.Columns.Count; i++)
                            {
                                sqlcopy.ColumnMappings.Add(i, dt.Columns[i].ColumnName);
                            }
                            foreach (DataRow dr in dt.Rows)
                            {
                                command.CommandText = "select count(*) from product where DeleteTime is null and  TypeId=0 and Memo='" + dr["Memo"].ToString() + "'";
                                var result = command.ExecuteScalar();
                                if (result == null || result.ToString() == "0")
                                {
                                    productsdt.Rows.Add(dr.ItemArray);
                                }
                            }
                        }
                        //处理 取消重复数据
                        if (productsdt != null && productsdt.Rows.Count > 0)
                        {
                            sqlcopy.WriteToServer(productsdt);
                            transaction.Commit();
                        }
                    }
                    exm = "";
                    return true;
                }
                catch (Exception ex)
                {
                    exm = ex.Message;
                    conn.Close();
                    return false;
                    // transaction.Rollback(); 
                    // throw ex; 

                }
            }
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(jutehui.Model.Product model)
        {
            string dtime = DateTime.Now.AddDays(-10).ToString("yyyy-MM-dd HH:mm:ss");
          
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Product set ");
            strSql.Append("DeleteTime='" + DateTime.Now + "'");

            strSql.Append(" where UpdateTimeStr <'" + dtime + "' and DeleteTime is null and TypeId!=3");
            SqlParameter[] parameters = {
					 
					new SqlParameter("@DeleteTime", SqlDbType.DateTime)
				  }; 
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据  异常数据下架操作
        /// </summary>
        public bool Delete(long ProductId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Product set Down=1");
            strSql.Append(" where ProductId=@ProductId");
            SqlParameter[] parameters = {
					new SqlParameter("@ProductId", SqlDbType.BigInt)
			};
            parameters[0].Value = ProductId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string ProductIdlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Product set Down=1 ");
            strSql.Append(" where ProductId in (" + ProductIdlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public jutehui.Model.Product GetModel(long ProductId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ProductId,Pname,Plink,Price,ImgUrl,Description,SoldCount,Source,UpdateTime,Memo,FromWeb,ProductTypeId,TypeId,PriceType,UpdateTimeStr,ImgUrl2,ImgUrl3,ImgUrl4 from Product ");
            strSql.Append(" where ProductId=@ProductId");
            SqlParameter[] parameters = {
					new SqlParameter("@ProductId", SqlDbType.BigInt)
			};
            parameters[0].Value = ProductId;

            jutehui.Model.Product model = new jutehui.Model.Product();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public jutehui.Model.Product DataRowToModel(DataRow row)
        {
            jutehui.Model.Product model = new jutehui.Model.Product();
            if (row != null)
            {
                if (row["ProductId"] != null && row["ProductId"].ToString() != "")
                {
                    model.ProductId = long.Parse(row["ProductId"].ToString());
                }
                if (row["Pname"] != null)
                {
                    model.Pname = row["Pname"].ToString();
                }
                if (row["Plink"] != null)
                {
                    model.Plink = row["Plink"].ToString();
                }
                if (row["Price"] != null)
                {
                    model.Price = row["Price"].ToString();
                }
                if (row["ImgUrl"] != null)
                {
                    model.ImgUrl = row["ImgUrl"].ToString();
                }
                if (row["Description"] != null)
                {
                    model.Description = row["Description"].ToString();
                }
                if (row["SoldCount"] != null && row["SoldCount"].ToString() != "")
                {
                    model.SoldCount = int.Parse(row["SoldCount"].ToString());
                }
                if (row["Source"] != null)
                {
                    model.Source = row["Source"].ToString();
                }
                if (row["UpdateTime"] != null && row["UpdateTime"].ToString() != "")
                {
                    model.UpdateTime = DateTime.Parse(row["UpdateTime"].ToString());
                }
                if (row["Memo"] != null)
                {
                    model.Memo = row["Memo"].ToString();
                }
                if (row["FromWeb"] != null)
                {
                    model.FromWeb = row["FromWeb"].ToString();
                }
                if (row["ProductTypeId"] != null && row["ProductTypeId"].ToString() != "")
                {
                    model.ProductTypeId = long.Parse(row["ProductTypeId"].ToString());
                }
                if (row["TypeId"] != null && row["TypeId"].ToString() != "")
                {
                    model.TypeId = long.Parse(row["TypeId"].ToString());
                }
                if (row["PriceType"] != null)
                {
                    model.PriceType = row["PriceType"].ToString();
                }
                if (row["UpdateTimeStr"] != null)
                {
                    model.UpdateTimeStr = row["UpdateTimeStr"].ToString();
                }
                if (row["ImgUrl2"] != null)
                {
                    model.ImgUrl2 = row["ImgUrl2"].ToString();
                }
                if (row["ImgUrl3"] != null)
                {
                    model.ImgUrl3 = row["ImgUrl3"].ToString();
                }
                if (row["ImgUrl4"] != null)
                {
                    model.ImgUrl4 = row["ImgUrl4"].ToString();
                }

            }
            return model;
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ProductId,Pname,Plink,Price,ImgUrl,Description,SoldCount,Source,UpdateTime,Memo,FromWeb,ProductTypeId,TypeId,PriceType,UpdateTimeStr,ImgUrl2,ImgUrl3,ImgUrl4 ");
            strSql.Append(" FROM Product where Plink not like '%https://detail.ju.taobao.com/home.htm?id%' and DeleteTime is null and (Down is null or Down=0)");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ProductId,Pname,Plink,Price,ImgUrl,Description,SoldCount,Source,UpdateTime,Memo,FromWeb,ProductTypeId,TypeId,PriceType,UpdateTimeStr,ImgUrl2,ImgUrl3,ImgUrl4 ");
            strSql.Append(" FROM Product ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Product ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表  szr
        /// </summary>
        public DataSet GetListByPage(string strWhere, int productId, int pageSize)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT top " + pageSize + " * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");

            strSql.Append("order by T.ProductId desc");

            strSql.Append(")AS Row, T.*  from Product T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append("Where  T.DeleteTime is null and Plink not like '%https://detail.ju.taobao.com/home.htm?id%' and (T.Down is null or T.Down=0 ) and " + strWhere);
            }
            strSql.Append(" ) TT");
            //   strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 分页获取数据列表  szr  淘宝九块九 特卖
        /// </summary>
        public DataSet GetListByPageTeMai(string strWhere, int productId, int pageSize)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT top " + pageSize + " * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");

            strSql.Append("order by T.ProductId desc");

            strSql.Append(")AS Row, T.*  from Product T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append("Where  T.DeleteTime is null and TypeId=3 and (T.Down is null or T.Down=0) and  " + strWhere);
            }
            strSql.Append(" ) TT");
            //   strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }


        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "Product";
            parameters[1].Value = "ProductId";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
        #region  ExtensionMethod

        #endregion  ExtensionMethod
    }
}

